package com.common.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

/**
 * @ClassName: ImportExcelUtil
 * @Description: excel 导入数据
 */
public class ImportExcelUtil { 
	public static List<List<Object>> importExcel(File file,int titleLength) throws IOException{ 
		String fileName = file.getName();    
		String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);    
		if("xls".equals(extension)){ 
			return read2003Excel(file,titleLength);    
		}else if("xlsx".equals(extension)){ 
			return read2007Excel(file,titleLength);    
		}else{ 
			throw new IOException("不支持的文件类型");    
		} 
	} 
	
	/**  
    * 读取 office 2003 excel  
    * @throws IOException   
    * @throws FileNotFoundException 
    */ 
	private static List<List<Object>> read2003Excel(File file, int titleLenght) throws IOException{ 
		List<List<Object>> list = new LinkedList<List<Object>>();    
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));    
		HSSFSheet sheet = hwb.getSheetAt(0);    
		Object value = null;    
		HSSFRow row = null;    
		HSSFCell cell = null;  
		int length = sheet.getRow(titleLenght).getLastCellNum(); // 获取标题的长度
		for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){    
			row = sheet.getRow(i);    
			if (row == null) { 
				continue;    
			} 
			List<Object> linked = new LinkedList<Object>();    
			for (int j = 0; j < length; j++) {    
				cell = row.getCell(j);    
				if (cell == null) { 
					linked.add("");
					continue;    
				} 
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    
				DecimalFormat nf = new DecimalFormat("0");// 格式化数字    
				switch (cell.getCellType()) { 
				case XSSFCell.CELL_TYPE_STRING: 
					// System.out.println(i+"行"+j+" 列 is String type");    
					value = cell.getStringCellValue();    
					break;    
				case XSSFCell.CELL_TYPE_NUMERIC: 
					// System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
					if("@".equals(cell.getCellStyle().getDataFormatString())){ 
						value = df.format(cell.getNumericCellValue());    
					} else if("General".equals(cell.getCellStyle().getDataFormatString())){ 
						value = nf.format(cell.getNumericCellValue());    
					}else{ 
						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
					} 
					break;   
				case XSSFCell.CELL_TYPE_BOOLEAN: 
					// System.out.println(i+"行"+j+" 列 is Boolean type");    
					value = cell.getBooleanCellValue();    
					break;    
				case XSSFCell.CELL_TYPE_BLANK: 
					// System.out.println(i+"行"+j+" 列 is Blank type");    
					value = "";    
					break;    
				default: 
					// System.out.println(i+"行"+j+" 列 is default type");    
					value = cell.toString();    
				} 
				// 注释这里之后空值也可以添加
				/*if (value == null || "".equals(value)) { 
					continue;    
				} */
				linked.add(value);      
			} 
			list.add(linked);    
		} 
		return list;    
	}
	
	/**  
    * 读取Office 2007 excel  
    */ 
	private static List<List<Object>> read2007Excel(File file,int titleLenght) throws IOException { 
		List<List<Object>> list = new LinkedList<List<Object>>();    
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径 
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));    
		// 读取第一章表格内容 
		XSSFSheet sheet = xwb.getSheetAt(0);    
		Object value = null;    
		XSSFRow row = null;    
		XSSFCell cell = null; 
		int length = sheet.getRow(titleLenght).getLastCellNum();// 获取标题的长度
		for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
			row = sheet.getRow(i);    
			if (row == null) {
				continue;    
			} 
			List<Object> linked = new LinkedList<Object>();    
			for (int j = 0; j < length; j++) {    
				cell = row.getCell(j);    
				if (cell == null) { 
					linked.add("");
					continue;    
				} 
				DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符    
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串    
				DecimalFormat nf = new DecimalFormat("0");// 格式化数字   
				switch (cell.getCellType()) { 
				case XSSFCell.CELL_TYPE_STRING: 
					// System.out.println(i+"行"+j+" 列 is String type");    
					value = cell.getStringCellValue();    
					break;    
				case XSSFCell.CELL_TYPE_NUMERIC: 
					// System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());    
					if("@".equals(cell.getCellStyle().getDataFormatString())){ 
						value = df.format(cell.getNumericCellValue());    
					} else if("General".equals(cell.getCellStyle().getDataFormatString())){ 
						value = nf.format(cell.getNumericCellValue());    
					}else{ 
						value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));    
					} 
					break;    
				case XSSFCell.CELL_TYPE_BOOLEAN: 
					// System.out.println(i+"行"+j+" 列 is Boolean type");    
					value = cell.getBooleanCellValue();    
					break;    
				case XSSFCell.CELL_TYPE_BLANK: 
					// System.out.println(i+"行"+j+" 列 is Blank type");    
					value = "";    
					break;    
				default: 
					// System.out.println(i+"行"+j+" 列 is default type");    
					value = cell.toString();    
				} 
				// 注释这里之后空值也可以添加
				/*if (value == null || "".equals(value)) { 
					continue;    
				} */
				linked.add(value);    
			} 
			list.add(linked);    
		} 
		return list;    
	} 
	
	
	public static void main(String[] args) throws IOException { 
		// excel 导入数据demo 
		File file = new File("H:\\工资.xls");  
		List<List<Object>> dataList = importExcel(file,1);//1 为标题的位置  
		for (int i = 2; i < dataList.size(); i++) {
			if(dataList.get(i).size()>0){
				for (int j = 1; j < dataList.get(i).size(); j++) { 
					System.out.println(dataList.get(i).get(j));
				} 
				System.out.println("------------------"); 
			}		
		} 
	}
	
}


